#!/usr/bin/env python3
#-*- coding:utf-8 -*-
import pymysql
import taos
import time

con_mysql = pymysql.connect(host='127.0.0.1',
                        database='locaiong', 
                        user='root', 
                        password='password',
                        charset='utf8')

con_taos = taos.connect(host="127.0.0.1",
                        user="root",
                        password="password",
                        database="locaiong",
                        port=6030,
                        config="/etc/taos",
                        timezone="Asia/Shanghai")

"""
DROP DATABASE IF EXISTS location; 
CREATE DATABASE location;
ALTER DATABASE location CACHEMODEL 'both';
ALTER DATABASE location CACHESIZE 16;
ALTER DATABASE location KEEP 120;

DROP TABLE IF EXISTS location.location;
CREATE STABLE location.location ( \
    locationtime TIMESTAMP, \
    lat DOUBLE, \
    lng DOUBLE, \
    speed DOUBLE) \
    TAGS (loginname NCHAR(50), \
      username NCHAR(50), \
      deviceimei NCHAR(50), \
      devicemodel NCHAR(100), \
      devicetype BOOL);
"""

start_time = time.time()
cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
cursor_mysql.execute('SELECT Id FROM location.location ORDER BY Id DESC LIMIT 1;')
countlines = cursor_mysql.fetchall()
cursor_mysql.close()
count = countlines[0]['Id']

i = 0
j = 10000
taoscnt = 0
for i in range(0, count, 10000):
    cursor_mysql = con_mysql.cursor()
    cursor_mysql.execute('SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname , \
        LoginName, \
        MAX(UserName) AS UserName, \
        DeviceIMEI, \
        IFNULL(MAX(DeviceModel),\'\') AS DeviceModel, \
        IFNULL(MAX(DeviceType),\'\') AS DeviceType\
        FROM location.location \
        WHERE Id > %d \
        AND Id <= %d \
        GROUP BY LoginName,DeviceIMEI;' % (i,j))
    tbnamecur = cursor_mysql.fetchall()
    cursor_mysql.close()

    for tbname,loginname,username,deviceimei,devicemodel,devicetype in tbnamecur:
        cursor_taos = con_taos.cursor()
        cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
        cursor_mysql.execute(f'SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname, \
          LocationTime, \
          Lat, \
          Lng, \
          Speed \
        FROM location.location \
        WHERE LoginName = \"%s\" \
          AND Id > %d \
          AND Id <= %d;' % (loginname,i,j))
        submission = cursor_mysql.fetchall()
        cursor_mysql.close()
        sql="INSERT INTO location.`{}` USING location.location TAGS (\'{}\',\'{}\',\'{}\',\'{}\',{}) VALUES ".format(tbname,loginname,username,deviceimei,devicemodel,devicetype).replace(",)" ,",False)" )
        if submission:
            for row in submission:
                sql += '(\'{}\',{},{},{}) '.format(row["LocationTime"],row["Lat"],row["Lng"],row["Speed"])
            sql += ";"
            #print(sql)
            cursor_taos.execute(sql)
            taoscnt += cursor_taos.rowcount
            stime = time.time() - start_time
            avgrow = int(taoscnt / stime)
            print("数据已迁移%d行,耗时%d秒,平均行%d/秒" % (taoscnt,stime,avgrow))
        cursor_taos.close()  
    else:
        i += 10000
        j += 10000
        continue
i += 10000
j += 10000

end_time = time.time()
deltatime = end_time - start_time
totalhour = int(deltatime / 3600)
totalminute = int((deltatime - totalhour * 3600) / 60)
totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)
print("数据全部迁移完毕,总计耗时:%d小时%d分%d秒!" %(totalhour, totalminute, totalsecond))

con_mysql.close()
con_taos.close()